package com.web.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.jorm.Session;
import org.jorm.exception.JormptException;

import com.build.SessionBuilder;
import com.web.model.News;
import com.web.model.dao.NewsDao;
import com.web.model.dao.impl.NewsDaoImpl;
import com.web.tag.bean.PageModel;

public class NewsService {

	public List<News> searchNews(String keyword, PageModel model) {
		List<News> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			NewsDao newsDao = new NewsDaoImpl();
			String whereSql = " where " + News.COLUMN_TITLE + " like '%"
					+ keyword + "%' or " + News.COLUMN_CONTENT_HTML
					+ " like '%" + keyword + "%'";
			String sql = "select * from " + News.TABLE_NAME + whereSql;
			if (model != null) {
				try {
					model.setRecodeZong(newsDao.getResultCount(
							"select count(*) as ct from " + News.TABLE_NAME
									+ " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from ("
						+ sql + ") row_ where rownum <= " + row_x
						+ ") where rownum_ > " + row_y;
			}
			list = getList(sql);
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<News> listNewsByCat(int categoryId, PageModel model) {
		List<News> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			NewsDao newsDao = new NewsDaoImpl();
			String whereSql = " where " + News.COLUMN_CLASSID + " ="
					+ categoryId + " order by " + News.COLUMN_SORTNO + " ";
			String sql = "select * from " + News.TABLE_NAME + whereSql;
			if (model != null) {
				try {
					model.setRecodeZong(newsDao.getResultCount(
							"select count(*) as ct from " + News.TABLE_NAME
									+ " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from ("
						+ sql + ") row_ where rownum <= " + row_x
						+ ") where rownum_ > " + row_y;
			}
			list = getList(sql);
			 
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<News> getList(String sql) {
		List list = new ArrayList();
		Session session;
		try {
			session = SessionBuilder.oracleSession();

			Connection conn = session.connection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			News obj=null;
			while (rs.next()){
				obj = new News();
				obj.setAddtime(rs.getTimestamp(News.COLUMN_ADDTIME));
				obj.setAuthorId(rs.getInt(News.COLUMN_AUTHOR_ID));
				obj.setBrowse(rs.getInt(News.COLUMN_BROWSE));
				obj.setClassid(rs.getInt(News.COLUMN_CLASSID));
				obj.setContentHtml(rs.getClob(News.COLUMN_CONTENT_HTML));
				obj.setContentText(rs.getClob(News.COLUMN_CONTENT_TEXT));
				obj.setCreateTime(rs.getTimestamp(News.COLUMN_CREATE_TIME));
				obj.setDtitle(rs.getString(News.COLUMN_DTITLE));
				obj.setFromName(rs.getString(News.COLUMN_FROM_NAME));
				obj.setFromUrl(rs.getString(News.COLUMN_FROM_URL));
				obj.setId(rs.getInt(News.COLUMN_ID));
				obj.setKeyword(rs.getString(News.COLUMN_KEYWORD));
				obj.setRecom(rs.getInt(News.COLUMN_RECOM));
				obj.setSortno(rs.getInt(News.COLUMN_SORTNO));
				obj.setState(rs.getInt(News.COLUMN_STATE));
				obj.setTitle(rs.getString(News.COLUMN_TITLE));
				obj.setTopState(rs.getInt(News.COLUMN_TOP_STATE));
				list.add(obj);
			}
			rs.close();
			ps.close();
			session.close();
		 
		} catch (JormptException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public News getById(int id) {
		News obj = null;
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + News.TABLE_NAME + " where " + News.COLUMN_ID + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new News();
				obj.setAddtime(rs.getTimestamp(News.COLUMN_ADDTIME));
				obj.setAuthorId(rs.getInt(News.COLUMN_AUTHOR_ID));
				obj.setBrowse(rs.getInt(News.COLUMN_BROWSE));
				obj.setClassid(rs.getInt(News.COLUMN_CLASSID));
				obj.setContentHtml(rs.getClob(News.COLUMN_CONTENT_HTML));
				obj.setContentText(rs.getClob(News.COLUMN_CONTENT_TEXT));
				obj.setCreateTime(rs.getTimestamp(News.COLUMN_CREATE_TIME));
				obj.setDtitle(rs.getString(News.COLUMN_DTITLE));
				obj.setFromName(rs.getString(News.COLUMN_FROM_NAME));
				obj.setFromUrl(rs.getString(News.COLUMN_FROM_URL));
				obj.setId(rs.getInt(News.COLUMN_ID));
				obj.setKeyword(rs.getString(News.COLUMN_KEYWORD));
				obj.setRecom(rs.getInt(News.COLUMN_RECOM));
				obj.setSortno(rs.getInt(News.COLUMN_SORTNO));
				obj.setState(rs.getInt(News.COLUMN_STATE));
				obj.setTitle(rs.getString(News.COLUMN_TITLE));
				obj.setTopState(rs.getInt(News.COLUMN_TOP_STATE));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}

}
